/**
 * 
 */
package com.py.credit.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import com.py.credit.entity.TableResult;
import com.py.credit.entity.common.OptionalColumn;

/**
 * @author dd
 *
 */
public class ExcelUtil {
	private static final Logger LOG = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 
	 * @param response
	 * @param data
	 *            导出数据
	 * @param fileNanme
	 *            文件名
	 * @param sheetName
	 *            sheet名
	 * @param showColumns
	 *            展示列
	 * @param allColumns
	 *            列全部信息
	 * @throws IOException
	 */
	public static void downloadExcel(HttpServletResponse response, List<Map<String, Object>> data, String fileNanme,
			String sheetName, List<String> showColumns, List<OptionalColumn> allColumns) throws IOException {

		response.setContentType("application/vnd.ms-excel");
		HSSFWorkbook workbook = null;
		OutputStream fOut = null;
		// 进行转码，使其支持中文文件名
		fileNanme = URLEncoder.encode(fileNanme, "UTF-8");
		response.setHeader("content-disposition", "attachment;filename=" + fileNanme + ".xls");
		// 产生工作簿对象
		workbook = new HSSFWorkbook();
		// 产生工作表对象
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, sheetName);
		HSSFRow row = null;
		HSSFCell cell = null;
		List<String> strBT = new ArrayList<String>();
		for (String colEN : showColumns) {
			for (OptionalColumn col : allColumns) {
				if (colEN.equals(col.getColNameEN())) {
					strBT.add(col.getColNameCN());
					break;
				}
			}
		}

		// 创建标题行
		row = sheet.createRow(0);
		for (int i = 0; i < strBT.size(); i++) {
			cell = row.createCell(i);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(strBT.get(i));
		}
		for (int i = 0; i < data.size(); i++) {
			row = sheet.createRow(i + 1);// 创建一行
			for (int j = 0; j < strBT.size(); j++) {
				cell = row.createCell(j);// 创建一列
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				String showColEN = showColumns.get(j);
				Object object = data.get(i).get(showColEN);
				String widget = null;
				String type = null;
				Map<String, String> widgetValue = null;
				for (OptionalColumn col : allColumns) {
					if (showColEN.equals(col.getColNameEN())) {
						widget = col.getWidget();
						widgetValue = col.getWidgetValue();
						type = col.getType();
						break;
					}
				}

				try {

					if (object != null) {

						String columValue = object.toString();

						if ("select".equals(widget) && widgetValue != null) {

							columValue = widgetValue.get(columValue) != null ? widgetValue.get(columValue) : "";
						} else if ("timestamp".equals(type) && columValue.lastIndexOf(".") != -1) {

							columValue = columValue.substring(0, columValue.lastIndexOf("."));

						}
						cell.setCellValue(columValue);

					}
				} catch (Exception e) {
					LOG.error("数据导出错:" + showColEN + "====>", e);
				}
			}

		}

		fOut = response.getOutputStream();
		workbook.write(fOut);
		workbook.close();
		fOut.flush();
		fOut.close();

	}

	/**
	 * @param response
	 * @param table
	 * @throws IOException
	 */
	public static void downloadTemplate(HttpServletResponse response, TableResult table) throws IOException {
		response.setContentType("application/vnd.ms-excel");
		HSSFWorkbook workbook = null;
		OutputStream fOut = null;
		// 进行转码，使其支持中文文件名
		String fileNanme = URLEncoder.encode(table.getTableNameCN(), "UTF-8");
		response.setHeader("content-disposition", "attachment;filename=" + fileNanme + ".xls");
		// 产生工作簿对象
		workbook = new HSSFWorkbook();
		// 产生工作表对象
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, table.getTableNameCN());
		HSSFRow row = null;
		HSSFCell cell = null;
		row = sheet.createRow(0);

		if (!CollectionUtils.isEmpty(table.getColumns())) {
			for (int i = 0; i < table.getColumns().size(); i++) {
				cell = row.createCell(i);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(table.getColumns().get(i).getMetricName());
			}
		}

		fOut = response.getOutputStream();
		workbook.write(fOut);
		workbook.close();
		fOut.flush();
		fOut.close();

	}

}
